package com.boss.salary.parsingExcel.impl;

import com.boss.framework.exception.AppRuntimeException;
import com.boss.salary.dao.IAgencyInfoDao;
import com.boss.salary.dao.ISalaryMonthEndDao;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.service.IFormulaManageService;
import com.boss.salary.service.IRedisService;
import com.boss.salary.service.impl.SalaryHistoryAsyncServiceImpl;
import com.boss.salary.vo.AgencySalaryVO;
import com.boss.salary.vo.SalaryMonthEndVO;
import com.singularsys.jep.JepException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.NumberFormat;
import java.util.*;


@Service
public class ReadSalaryReportExcel {
    private static final Logger logger = LoggerFactory.getLogger(SalaryHistoryAsyncServiceImpl.class);

    @Autowired
    private IAgencyInfoDao iAgencyInfoDao;
    @Autowired
    private ReadSalaryReportExcelAsync readSalaryReportExcelAsync;
    @Autowired
    private ISalaryMonthEndDao iSalaryMonthEndDao;
    @Autowired
    private IFormulaManageService formulaManageService;
    @Autowired
    private IRedisService redisService;

    @Transactional(rollbackFor = Exception.class)
    public void Read(MultipartFile file, String province, String agencyId, List<AgencySalaryVO> exccelColumnName)  throws AppRuntimeException,Exception {

        String code  = iAgencyInfoDao.getCodeByAgencyId(agencyId);
        List<String> slist= new ArrayList<>();
        //获取文件流
        BufferedInputStream fis = new BufferedInputStream(file.getInputStream());
        //获取文件名
        String fileName= file.getOriginalFilename();

        String substring = fileName.substring(fileName.lastIndexOf("."));
        //判断版本（07，03）
        Workbook workbook = ".xls".equals(substring) ? new HSSFWorkbook(fis) : new XSSFWorkbook(fis);
        try{
            readExcel(province, agencyId, code, exccelColumnName, slist, fis, workbook);
        }catch (AppRuntimeException e){
            throw e;
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public Map<String,String> ReadAsync(MultipartFile file, String province, String agencyId, List<AgencySalaryVO> exccelColumnName)  {
        Map<String,String>  remap=new HashMap<>();
        Object re = redisService.get(province+agencyId);
        if (re != null) {
            remap.put("Executing",province+agencyId);
            return remap;
        }
        try {
            //获取文件流
            BufferedInputStream fis = new BufferedInputStream(file.getInputStream());
            //获取文件名
            String fileName= file.getOriginalFilename();
            readSalaryReportExcelAsync.ReadAsync(fis,fileName,province,agencyId,exccelColumnName);
        } catch (Exception exception) {
            redisService.put(province+agencyId,"F-"+exception.getMessage());
            exception.printStackTrace();
        }

        redisService.put(province+agencyId,0);

        remap.put("guid",province+agencyId);

        return remap;
    }

    private void readExcel(String province, String agencyId, String code,
                           List<AgencySalaryVO> exccelColumnName, List<String> slist,
                           BufferedInputStream fis, Workbook workbook) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, IOException, JepException {
        List<SalaryMonthEndVO> salaryMonthEndVOs =new ArrayList<>();
        List<String> agencyIds = Collections.singletonList(agencyId);
        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(0);
        if (rowTitle!=null) {
            //获取有效标题头
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    String cellValue = cell.getStringCellValue();
                    for (int i = 0; i < exccelColumnName.size(); i++) {
                        if(exccelColumnName.get(i).getSalaryName().equals(cellValue)){
                            slist.add(exccelColumnName.get(i).getColumnName());
                        }
                    }
                }
            }
            if(slist.size()!=cellCount-5){
                throw new AppRuntimeException("存在无法读取的工资项");
            }
        }

        // 获取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        //List<PersonExt> idAndMeritPayCode = iPersonInfoDao.getIdAndMeritPayCodeByIdenNo(agencyId);
        //通过agencyid查询这个单位下的所有工资项
       List<SalaryMonthEndVO> salaryMonthEndVOS=iSalaryMonthEndDao.getSalaryMonthEndVOByPersonId(agencyId);
        for (int rowNum = 1; rowNum < rowCount ; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            SalaryMonthEndVO salaryMonthEndVO= new SalaryMonthEndVO();
            if (rowData!=null){
                // 读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();

                for (int cellNum = 0; cellNum < cellCount ; cellNum++) {
                    if(cellNum==1){
                        Cell cell = rowData.getCell(cellNum);
                        String code1=getCellValue(cell).toString();
                        String[] array=code1.split("\\.");
                        code1=array[0];
                        if(!code.equals(code1)){
                            throw new AppRuntimeException("单位编码有误");
                        }else{
                            salaryMonthEndVO.setAgencyId(agencyId);
                        }
                    }
                    if(cellNum==3){
                        Cell cell = rowData.getCell(cellNum);
                        String idenNo=getCellValue(cell).toString();
                        //循环 salaryMonthEndFor命名
                        salaryMonthEndFor:for(SalaryMonthEndVO salaryMonthEndVO1:salaryMonthEndVOS){
                            //判断idenNo不为空并且 和idenNo相等
                        if(!StringUtils.isEmpty(idenNo)&&idenNo.equals(salaryMonthEndVO1.getIdenNo())){
                            salaryMonthEndVO=salaryMonthEndVO1;
                            break salaryMonthEndFor;
                        }
                        }
                        if(StringUtils.isEmpty(salaryMonthEndVO.getPersonId())){
                            throw new AppRuntimeException(idenNo+"身份证号有误");
                        }
                    }
                    if(cellNum>4){
                        Cell cell = rowData.getCell(cellNum);
                        Class<?> clazz = salaryMonthEndVO.getClass();
                        String strCell;
                        try {
                            if(cell==null){
                                strCell ="0";
                            }else if(cell.getCellTypeEnum()==CellType.STRING){
                                strCell = String.valueOf(cell.getStringCellValue());
                            }else{
                                strCell = String.valueOf(cell.getNumericCellValue());
                            }

                        } catch (IllegalStateException e) {
                            if(cell==null){
                                strCell ="0";
                            }else if(cell.getCellTypeEnum()==CellType.STRING){
                                strCell = String.valueOf(cell.getStringCellValue());
                            }else{
                                strCell = String.valueOf(cell.getNumericCellValue());
                            }
                        }
                        Method setMethod = clazz.getDeclaredMethod("set" +
                                slist.get(cellNum-5).substring(0, 1).toUpperCase() +  slist.get(cellNum-5).substring(1),Double.class);
                        //setMethod.invoke(salaryMonthEndVO,Double.valueOf(getCellValueByCell(cell)));
                        setMethod.invoke(salaryMonthEndVO,Double.valueOf(strCell));
                    }
                }
                salaryMonthEndVOs.add(salaryMonthEndVO);
            }
        }
        salaryMonthEndVOs = formulaManageService.calculateByFormula(province, agencyIds, salaryMonthEndVOs);
        iSalaryMonthEndDao.batchUpdateSalaryMonthEnd(salaryMonthEndVOs);
        fis.close();
//        List<String> addList=iSalaryBasedataDao.getAlladdColum();
//        List<String> subList=iSalaryBasedataDao.getAllsubColum();
//        iSalaryMonthEndDao.updateRealAndWagesAndSub(agencyId,addList,subList);
    }


    //获取单元格各类型值，返回字符串类型
//    private static String getCellValueByCell(Cell cell) {
//        //判断是否为null或空串
//        if (cell==null || cell.toString().trim().equals("")) {
//            return "0";
//        }
//        String cellValue = "";
//        int cellType=cell.getCellType();
//        switch (cellType) {
//            case Cell.CELL_TYPE_STRING: //字符串类型
//                cellValue= cell.getStringCellValue().trim();
//                cellValue=StringUtils.isEmpty(cellValue) ? "" : cellValue;
//                break;
//            case Cell.CELL_TYPE_BOOLEAN:  //布尔类型
//                cellValue = String.valueOf(cell.getBooleanCellValue());
//                break;
//            case Cell.CELL_TYPE_NUMERIC: //数值类型
//                cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
//                break;
//            default: //其它类型，取空串吧
//                cellValue = "0";
//                break;
//        }
//        return cellValue;
//    }


    public static Object getCellValue(Cell cell) {
        NumberFormat numberFormat = NumberFormat.getInstance();
        numberFormat.setGroupingUsed(false);
        if (cell==null || cell.toString().trim().equals("")) {
            return "0";
        }
        Object cellValue = null;
        CellType cellType = cell.getCellTypeEnum();// CellType.forInt(cell.getCellType());
        if (cellType == CellType.STRING) {
            cellValue = cell.getStringCellValue();
        } else if (cellType == CellType.NUMERIC) {
            cellValue =numberFormat.format(cell.getNumericCellValue());
//            if (DateUtil.isCellDateFormatted(cell)) {
//                cellValue = cell.getDateCellValue();
//            } else {
//                cellValue = cell.getNumericCellValue();
//            }
        } else if (cellType == CellType.BOOLEAN) {
            cellValue = cell.getBooleanCellValue();
        } else if (cellType == CellType.FORMULA) {
            cellValue = cell.getCellFormula();
        } else if (cellType == CellType.BLANK) {
            cellValue = "0";
        }
        return cellValue;
    }


    /**
     * 查询导入全部工资
     * @param file
     * @param province
     * @param exccelColumnName
     * @throws AppRuntimeException
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public void Reads(MultipartFile file, String province, List<AgencySalaryVO> exccelColumnName)  throws AppRuntimeException,Exception {
        List<AgencyVerify> agencyVerifies  = iAgencyInfoDao.getCodeByAgency(province);
        List<String> slist= new ArrayList<>();
        //获取文件流
        BufferedInputStream fis = new BufferedInputStream(file.getInputStream());
        //获取文件名
        String fileName= file.getOriginalFilename();

        String substring = fileName.substring(fileName.lastIndexOf("."));
        //判断版本（07，03）
        Workbook workbook = ".xls".equals(substring) ? new HSSFWorkbook(fis) : new XSSFWorkbook(fis);
        try{
            readExcels(province, agencyVerifies, exccelColumnName, slist, fis, workbook);
        }catch (AppRuntimeException e){
            throw e;
        }
    }
    private void readExcels(String province, List<AgencyVerify> agencyVerifies,
                           List<AgencySalaryVO> exccelColumnName, List<String> slist,
                           BufferedInputStream fis, Workbook workbook) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, IOException, JepException {
        List<SalaryMonthEndVO> salaryMonthEndVOs =new ArrayList<>();
        List<String> codeList =new ArrayList<>();

        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(0);
        if (rowTitle!=null) {
            //获取有效标题头
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    String cellValue = cell.getStringCellValue();
                    for (int i = 0; i < exccelColumnName.size(); i++) {
                        if(exccelColumnName.get(i).getSalaryName().equals(cellValue)){
                            slist.add(exccelColumnName.get(i).getColumnName());
                        }
                    }
                }
            }
            if(slist.size()!=cellCount-5){
                throw new AppRuntimeException("存在无法读取的工资项");
            }
        }

        // 获取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        logger.info("==================数据查询开始Begin ================");
        //查询所有工资项
        List<SalaryMonthEndVO> salaryMonthEndVOS=iSalaryMonthEndDao.getSalaryMonthEndVOByPerson(province);
        logger.info("==================数据查询结束end================");
        logger.info("==================导入开始Begin ================");
        for (int rowNum = 1; rowNum < rowCount ; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            SalaryMonthEndVO salaryMonthEndVO= new SalaryMonthEndVO();
            //salaryMonthEndVO2 用于判断单位code是否错误
            String agencyCode="";
            if (rowData!=null){
                // 读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();

                for (int cellNum = 0; cellNum < cellCount ; cellNum++) {
                    if(cellNum==1){
                        Cell cell = rowData.getCell(cellNum);
                        String code1=getCellValue(cell).toString();
                        String[] array=code1.split("\\.");
                        code1=array[0];
                        agencyCode=code1;
                        agencyVerifyFor:for(AgencyVerify agencyVerify : agencyVerifies){
                            if(!StringUtils.isEmpty(code1)&&code1.equals(agencyVerify.getAgencyCode())){
                                //1是上报单位非1是未上报单位
                            if(!agencyVerify.getIsSubmitInAgency().equals(1)){
                                    salaryMonthEndVO.setAgencyId(agencyVerify.getId());
                                break agencyVerifyFor;
                                }
                                }
                            if(agencyVerify.getAgencyCode().equals(code1)&&agencyVerify.getIsSubmitInAgency().equals(1)){
                                throw new AppRuntimeException(agencyVerify.getAgencyCode()+"-"+agencyVerify.getAgencyName()+"单位已上报!!!  请清空该单位人员重新导入");
                                }
                        }
                    }
                    if(cellNum==3){
                        Cell cell = rowData.getCell(cellNum);
                        String idenNo=getCellValue(cell).toString();
                        //循环 salaryMonthEndFor命名
                        salaryMonthEndFor:for(SalaryMonthEndVO salaryMonthEndVO1:salaryMonthEndVOS){
                            //判断idenNo不为空并且 和idenNo相等
                            if(!StringUtils.isEmpty(idenNo)&&idenNo.equals(salaryMonthEndVO1.getIdenNo())){
                                salaryMonthEndVO=salaryMonthEndVO1;
                                break salaryMonthEndFor;
                            }
                        }
                            if(StringUtils.isEmpty(salaryMonthEndVO.getPersonId())){
                                throw new AppRuntimeException(idenNo+"身份证号有误");
                        }
                    }
                    if(cellNum>4){
                        Cell cell = rowData.getCell(cellNum);
                        Class<?> clazz = salaryMonthEndVO.getClass();
                        String strCell;
                        try {
                            if(cell==null){
                                strCell ="0";
                            }else if(cell.getCellTypeEnum()==CellType.STRING){
                                strCell = String.valueOf(cell.getStringCellValue());
                            }else{
                                strCell = String.valueOf(cell.getNumericCellValue());
                            }
                        } catch (IllegalStateException e) {
                            if(cell==null){
                                strCell ="0";
                            }else if(cell.getCellTypeEnum()==CellType.STRING){
                                strCell = String.valueOf(cell.getStringCellValue());
                            }else{
                                strCell = String.valueOf(cell.getNumericCellValue());
                            }
                        }
                        Method setMethod = clazz.getDeclaredMethod("set" +
                                slist.get(cellNum-5).substring(0, 1).toUpperCase() +  slist.get(cellNum-5).substring(1),Double.class);
                        setMethod.invoke(salaryMonthEndVO,Double.valueOf(strCell));
                    }
                }
                salaryMonthEndVOs.add(salaryMonthEndVO);
                codeList.add(agencyCode);
            }
        }
        //校验单位编码是否有误
            List<Integer> agencyCodeList = new ArrayList<>();
            int count2 = 0;
            int count3;
            boolean flag1;
            for (int i = 0; i < codeList.size(); i++) {
                count3=0;
                flag1=true;
                for (int j = 0; j < agencyVerifies.size(); j++){
                    count3++;
                    if(agencyVerifies.get(j).getAgencyCode().equals(codeList.get(i))){
                        count2++;flag1=false;
                    }else if(flag1&&(count3==agencyVerifies.size())){ agencyCodeList.add(i);}
                }
            }

            if(codeList.size()!=count2){
                String agencyCode;
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < agencyCodeList.size(); i++) {
                    agencyCode = codeList.get(agencyCodeList.get(i));
                    if(i==agencyCodeList.size()-1){
                        sb.append(agencyCode).append(" ");
                    }else{
                        sb.append(agencyCode).append("，").append(" ");
                    }
                }
                String str = sb.toString();
                throw new AppRuntimeException("单位编码"+str+"有误");
            }

            List<String> agencyIds= new ArrayList<>();
            for(AgencyVerify agencyVerify: agencyVerifies){
                agencyIds.add(agencyVerify.getAgencyId());
            }

            logger.info("==================导入中!!! ================");
            salaryMonthEndVOs = formulaManageService.calculateByFormula(province, agencyIds, salaryMonthEndVOs);
            iSalaryMonthEndDao.batchUpdateSalaryMonthEnd(salaryMonthEndVOs);
            logger.info("==================导入结束 End ================");
            fis.close();
        }



}
